{
 "cells": [
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# 5. 多层索引",
   "id": "c64fd33f5abfdfbe"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T06:03:20.227427Z",
     "start_time": "2025-09-15T06:03:18.961138Z"
    }
   },
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "path = 'D:/2506A/monty03/day15/file/'"
   ],
   "id": "950439bca929d807",
   "outputs": [],
   "execution_count": 1
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 5.1 创建多层索引",
   "id": "104689beff64998d"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-12T08:15:54.942112Z",
     "start_time": "2025-09-12T08:15:54.931332Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df = pd.read_excel(path + '多层.xlsx',sheet_name='有序',index_col=[0,1])\n",
    "\n",
    "print(df)"
   ],
   "id": "bc627591bfe2a9e4",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       分数\n",
      "班级 学号    \n",
      "1班 a    1\n",
      "   b    2\n",
      "   c    3\n",
      "2班 a    4\n",
      "   b    5\n",
      "   c    6\n",
      "3班 a    7\n",
      "   b    8\n"
     ]
    }
   ],
   "execution_count": 12
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 2. 索引查询与切片",
   "id": "2329c91b3fa632b0"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-12T08:35:59.351904Z",
     "start_time": "2025-09-12T08:35:59.345187Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 选择特定层级的数据\n",
    "# 选择'1班'班的所有学生\n",
    "# print(df.loc['1班'])\n",
    "# print(df.loc['1班',:])\n",
    "\n",
    "# 选择特定的学生 2班b学生\n",
    "# print(df.loc['2班','b'])\n",
    "# print(df.loc[('2班','b'),:])\n",
    "\n",
    "## 使用slice选择范围 # 得到一班级 2班 的学生 a,和 b\n",
    "print(df.loc[(slice('1班','2班'),slice('a','b')),:])"
   ],
   "id": "92e4396f86115f2a",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "       分数\n",
      "班级 学号    \n",
      "1班 a    1\n",
      "   b    2\n",
      "2班 a    4\n",
      "   b    5\n"
     ]
    }
   ],
   "execution_count": 39
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 3. 处理无序索引",
   "id": "39481f1bb82d2c43"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-12T08:40:18.111976Z",
     "start_time": "2025-09-12T08:40:18.097003Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 检查索引是否有序\n",
    "print(df.index.is_monotonic_increasing) # 有序\n",
    "\n",
    "df1 = pd.read_excel(path + '多层.xlsx',sheet_name='无序',index_col=[0,1])\n",
    "print(df1)\n",
    "print(df1.index.is_monotonic_increasing) # 无序\n",
    "\n",
    "# 对无序的索引进行排序\n",
    "df1.sort_index(inplace=True,level='科目')\n",
    "print(df1)\n",
    "print(df1.index.is_monotonic_increasing)"
   ],
   "id": "d78eb81a9cb7d610",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "False\n",
      "        分数\n",
      "科目 得分     \n",
      "语文 最高   90\n",
      "   最低   50\n",
      "数学 最高  100\n",
      "   最低   60\n",
      "False\n",
      "        分数\n",
      "科目 得分     \n",
      "数学 最低   60\n",
      "   最高  100\n",
      "语文 最低   50\n",
      "   最高   90\n",
      "True\n"
     ]
    }
   ],
   "execution_count": 45
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 4. 多层索引的创建的方式【行】",
   "id": "836f089b97c56de2"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-12T09:00:35.131272Z",
     "start_time": "2025-09-12T09:00:35.118598Z"
    }
   },
   "cell_type": "code",
   "source": [
    "df = pd.read_excel(path + '多层.xlsx',sheet_name='有序')\n",
    "# print(df)\n",
    "\n",
    "# 使用数组创建多层索引\n",
    "arrays = [\n",
    "    ['1班', '1班', '1班', '2班', '2班', '2班', '3班', '3班' ],  # 班级\n",
    "    ['a', 'b', 'c', 'a', 'b', 'c', 'a', 'b']  # 学号\n",
    "]\n",
    "index1 = pd.MultiIndex.from_arrays(arrays,names=['班级','学号'])\n",
    "print(index1)\n",
    "\n",
    "# 将自定义的索引作用到df上\n",
    "df.index = index1\n",
    "print(df)\n"
   ],
   "id": "c55d05bbaf8c9010",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MultiIndex([('1班', 'a'),\n",
      "            ('1班', 'b'),\n",
      "            ('1班', 'c'),\n",
      "            ('2班', 'a'),\n",
      "            ('2班', 'b'),\n",
      "            ('2班', 'c'),\n",
      "            ('3班', 'a'),\n",
      "            ('3班', 'b')],\n",
      "           names=['班级', '学号'])\n",
      "       班级 学号  分数\n",
      "班级 学号           \n",
      "1班 a   1班  a   1\n",
      "   b   1班  b   2\n",
      "   c   1班  c   3\n",
      "2班 a   2班  a   4\n",
      "   b   2班  b   5\n",
      "   c   2班  c   6\n",
      "3班 a   3班  a   7\n",
      "   b   3班  b   8\n"
     ]
    }
   ],
   "execution_count": 67
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-12T08:55:11.934208Z",
     "start_time": "2025-09-12T08:55:11.925471Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 使用元组创建索引\n",
    "tuple1 = [\n",
    "    ('1班', 'a'),\n",
    "    ('1班', 'b'),\n",
    "    ('1班', 'c'),\n",
    "    ('2班', 'a'),\n",
    "    ('2班', 'b'),\n",
    "    ('2班', 'c'),\n",
    "    ('3班', 'a'),\n",
    "    ('3班', 'b')\n",
    "]\n",
    "\n",
    "index2 = pd.MultiIndex.from_tuples(tuple1,names=['班级','学号'])\n",
    "print(index2)\n",
    "\n",
    "# 将自定义索引作用到df\n",
    "df.set_index(index2,inplace=True)\n",
    "print(df)"
   ],
   "id": "1c1b233596038f34",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MultiIndex([('1班', 'a'),\n",
      "            ('1班', 'b'),\n",
      "            ('1班', 'c'),\n",
      "            ('2班', 'a'),\n",
      "            ('2班', 'b'),\n",
      "            ('2班', 'c'),\n",
      "            ('3班', 'a'),\n",
      "            ('3班', 'b')],\n",
      "           names=['班级', '学号'])\n",
      "       班级 学号  分数\n",
      "班级 学号           \n",
      "1班 a   1班  a   1\n",
      "   b   1班  b   2\n",
      "   c   1班  c   3\n",
      "2班 a   2班  a   4\n",
      "   b   2班  b   5\n",
      "   c   2班  c   6\n",
      "3班 a   3班  a   7\n",
      "   b   3班  b   8\n"
     ]
    }
   ],
   "execution_count": 57
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-12T09:00:17.720905Z",
     "start_time": "2025-09-12T09:00:17.707184Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# from_product\n",
    "df = pd.read_excel(path + '多层.xlsx',sheet_name='有序')\n",
    "product = [\n",
    "    ['1班','2班','3班'],\n",
    "    ['a','b','c']\n",
    "]\n",
    "\n",
    "index3 = pd.MultiIndex.from_product(product,names=['班级','学号'])\n",
    "print(index3)\n",
    "\n",
    "# 将index3作用到df上\n",
    "df.set_index(index3,inplace=True)\n",
    "print(df)"
   ],
   "id": "6180dde37955a50f",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "MultiIndex([('1班', 'a'),\n",
      "            ('1班', 'b'),\n",
      "            ('1班', 'c'),\n",
      "            ('2班', 'a'),\n",
      "            ('2班', 'b'),\n",
      "            ('2班', 'c'),\n",
      "            ('3班', 'a'),\n",
      "            ('3班', 'b'),\n",
      "            ('3班', 'c')],\n",
      "           names=['班级', '学号'])\n",
      "       班级 学号  分数\n",
      "班级 学号           \n",
      "1班 a   1班  a   1\n",
      "   b   1班  b   2\n",
      "   c   1班  c   3\n",
      "2班 a   2班  a   4\n",
      "   b   2班  b   5\n",
      "   c   2班  c   6\n",
      "3班 a   3班  a   7\n",
      "   b   3班  b   8\n",
      "   c   3班  c   9\n"
     ]
    }
   ],
   "execution_count": 66
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 5. 多层索引的创建的方式【列索引】",
   "id": "3634ac7a78ec160a"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-12T09:01:55.512664Z",
     "start_time": "2025-09-12T09:01:55.500287Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 创建行和列都是多层索引的DataFrame\n",
    "index = pd.MultiIndex.from_product([[2019, 2020], [5, 6]], names=['年', '月'])\n",
    "columns = pd.MultiIndex.from_product([['水果', '蔬菜'], ['苹果', '香蕉']],\n",
    "                                    names=['类别', '品名'])\n",
    "data = pd.DataFrame(np.random.randn(4, 4), index=index, columns=columns)\n",
    "print(\"多层行列索引DataFrame:\")\n",
    "print(data)"
   ],
   "id": "eec11c198a1d558b",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "多层行列索引DataFrame:\n",
      "类别            水果                  蔬菜          \n",
      "品名            苹果        香蕉        苹果        香蕉\n",
      "年    月                                        \n",
      "2019 5 -0.538452 -0.304454 -1.388663 -0.013845\n",
      "     6  0.971128  0.216786 -0.056824 -2.870426\n",
      "2020 5 -1.687572 -0.722500 -0.772976 -1.541638\n",
      "     6 -0.956511 -0.772597  0.096326 -1.011946\n"
     ]
    }
   ],
   "execution_count": 68
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## 6. 分层索引计算\n",
   "id": "124040f47eb67f1b"
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-12T09:11:32.600487Z",
     "start_time": "2025-09-12T09:11:32.589128Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 通过多层索引进行就散\n",
    "df = pd.read_excel(path + '销售.xlsx',header=[0,1])\n",
    "# print(df)\n",
    "\n",
    "# 通过多层索引进行计算\n",
    "print(df['土豆','销量'])\n",
    "print(df['倭瓜','销量'])\n",
    "\n",
    "print(f'土豆和倭瓜的总销售量是:\\n{df['土豆','销量'] + df['倭瓜','销量']}')"
   ],
   "id": "5aa2b4a446004671",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "0    10\n",
      "1    11\n",
      "Name: (土豆, 销量), dtype: int64\n",
      "0    20\n",
      "1    30\n",
      "Name: (倭瓜, 销量), dtype: int64\n",
      "土豆和倭瓜的总销售量是:\n",
      "0    30\n",
      "1    41\n",
      "dtype: int64\n"
     ]
    }
   ],
   "execution_count": 79
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-12T09:16:51.160231Z",
     "start_time": "2025-09-12T09:16:51.150590Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 分组计算\n",
    "# 按照种类计算\n",
    "df = pd.read_excel(path + '销售.xlsx',header=[0,1])\n",
    "# print(df.T.groupby(level=0).sum().T)\n",
    "\n",
    "# 使用groupby 进行分层汇总\n",
    "print(df)"
   ],
   "id": "6b883ccbd5a62d7b",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   土豆     倭瓜   \n",
      "   销量 毛利  销量 毛利\n",
      "0  10  5  20  6\n",
      "1  11  4  30  5\n"
     ]
    }
   ],
   "execution_count": 90
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T06:05:43.468977Z",
     "start_time": "2025-09-15T06:05:43.446169Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 使用groupby进行分层汇总\n",
    "df = pd.read_excel(path + '表/水果蔬菜数据.xlsx',header=[0, 1], index_col=[0, 1])\n",
    "# print(df.groupby(level='年').sum())\n",
    "print(df.groupby(level='月').sum())"
   ],
   "id": "78e59eac571a684d",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "类别   水果        蔬菜    \n",
      "品名   苹果   香蕉   土豆 西红柿\n",
      "月                    \n",
      "5   210  165  105  82\n",
      "6   250  185  125  93\n"
     ]
    }
   ],
   "execution_count": 3
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T06:13:29.951911Z",
     "start_time": "2025-09-15T06:13:29.916236Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 使用xs方法进行数据选择\n",
    "df = pd.read_excel(path + '表/水果蔬菜数据.xlsx',header=[0, 1], index_col=[0, 1])\n",
    "\n",
    "data = df.xs('香蕉',level='品名',axis=1)\n",
    "print(data)\n",
    "print('=' * 30)\n",
    "data2 = df.xs('水果',level='类别',axis=1)\n",
    "print(data2)"
   ],
   "id": "4564a79501f50336",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "类别      水果\n",
      "年    月    \n",
      "2019 5  80\n",
      "     6  90\n",
      "2020 5  85\n",
      "     6  95\n",
      "==============================\n",
      "品名       苹果  香蕉\n",
      "年    月         \n",
      "2019 5  100  80\n",
      "     6  120  90\n",
      "2020 5  110  85\n",
      "     6  130  95\n"
     ]
    }
   ],
   "execution_count": 9
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T06:20:46.019848Z",
     "start_time": "2025-09-15T06:20:45.985944Z"
    }
   },
   "cell_type": "code",
   "source": [
    "# 复杂计算演示\n",
    "df = pd.read_excel(path + '表/水果蔬菜数据.xlsx',header=[0, 1], index_col=[0, 1])\n",
    "data2 = df.xs('水果',level='类别',axis=1)\n",
    "print(data2)\n",
    "print('=' * 30)\n",
    "data2 = df.xs('水果',level='类别',axis=1).groupby(level='年').sum()\n",
    "print(data2)\n",
    "\n",
    "print('=' * 30)\n",
    "data2 = df.xs('水果',level='类别',axis=1).sum(axis=1)\n",
    "print(data2)\n"
   ],
   "id": "c3f4754714a3bbb4",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "品名       苹果  香蕉\n",
      "年    月         \n",
      "2019 5  100  80\n",
      "     6  120  90\n",
      "2020 5  110  85\n",
      "     6  130  95\n",
      "==============================\n",
      "品名     苹果   香蕉\n",
      "年             \n",
      "2019  220  170\n",
      "2020  240  180\n",
      "==============================\n",
      "年     月\n",
      "2019  5    180\n",
      "      6    210\n",
      "2020  5    195\n",
      "      6    225\n",
      "dtype: int64\n"
     ]
    }
   ],
   "execution_count": 25
  },
  {
   "metadata": {
    "ExecuteTime": {
     "end_time": "2025-09-15T06:35:42.267143Z",
     "start_time": "2025-09-15T06:35:42.233293Z"
    }
   },
   "cell_type": "code",
   "source": [
    "#  计算水果和蔬菜的销售比例\n",
    "df = pd.read_excel(path + '表/水果蔬菜数据.xlsx',header=[0, 1], index_col=[0, 1])\n",
    "print(df)\n",
    "fruit_sum = df.xs('水果',level='类别',axis=1).sum(axis=1)\n",
    "vegetable_sum = df.xs('蔬菜',level='类别',axis=1).sum(axis=1)\n",
    "\n",
    "# 5月 水果 / 5月 水果 + 5 月蔬菜  # 5月水果占比\n",
    "\n",
    "print(f'每个月水果销售占比:\\n{(fruit_sum / (fruit_sum + vegetable_sum) * 100).round(2).apply(lambda x:f'{x}%')}')\n",
    "print(f'每个月蔬菜销售占比:\\n{vegetable_sum / (fruit_sum + vegetable_sum) }')\n",
    "\n"
   ],
   "id": "8de5772bf61e64ab",
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "类别       水果      蔬菜    \n",
      "品名       苹果  香蕉  土豆 西红柿\n",
      "年    月                 \n",
      "2019 5  100  80  50  40\n",
      "     6  120  90  60  45\n",
      "2020 5  110  85  55  42\n",
      "     6  130  95  65  48\n",
      "每个月水果销售占比:\n",
      "年     月\n",
      "2019  5    66.67%\n",
      "      6    66.67%\n",
      "2020  5    66.78%\n",
      "      6    66.57%\n",
      "dtype: object\n",
      "每个月蔬菜销售占比:\n",
      "年     月\n",
      "2019  5    0.333333\n",
      "      6    0.333333\n",
      "2020  5    0.332192\n",
      "      6    0.334320\n",
      "dtype: float64\n"
     ]
    }
   ],
   "execution_count": 44
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 2
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython2",
   "version": "2.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
